/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Vector;

/**
 *
 * @author DELL INSPIRON N5010
 */
public class PurchaseBill 
{
    private int BillId;
    private String BillName;
    private  int VenderId;
    private java.util.Date BuildlDate;
    private int AccountId;
    private String State;
    private String Unit;
    private double  TotalCost;
    

    /**
     * @return the BillId
     */
    public int getBillId() {
        return BillId;
    }

    /**
     * @param BillId the BillId to set
     */
    public void setBillId(int BillId) {
        this.BillId = BillId;
    }

    /**
     * @return the VenderId
     */
    public int getVenderId() {
        return VenderId;
    }

    /**
     * @param VenderId the VenderId to set
     */
    public void setVenderId(int VenderId) {
        this.VenderId = VenderId;
    }

    /**
     * @return the BuildlDate
     */
    public java.util.Date getBuildlDate() {
        return BuildlDate;
    }

    /**
     * @param BuildlDate the BuildlDate to set
     */
    public void setBuildlDate(java.util.Date BuildlDate) {
        this.BuildlDate = BuildlDate;
    }

    /**
     * @return the AccountId
     */
    public int getAccountId() {
        return AccountId;
    }

    /**
     * @param AccountId the AccountId to set
     */
    public void setAccountId(int AccountId) {
        this.AccountId = AccountId;
    }
    public boolean Addpurchasebill()
    {
      boolean result=true;
      try
      {
          Connection con=ConecttoDatabase.getConnection();
          String query="insert into PurchaseBill values(?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps=con.prepareCall(query);
          ps.setString(1, this.BillName);
          ps.setInt(2, this.VenderId);
          ps.setInt(3, this.AccountId);
        
          java.sql.Date jsd=new Date(this.BuildlDate.getTime());
          ps.setDate(4,jsd );
          
          ps.setString(5, this.State);
          ps.setDouble(6, this.TotalCost);
           ps.setString(7, this.Unit);
          result=ps.executeUpdate()>0;
      }
      catch(Exception ex)
      {
          System.out.println(ex);
          result=false;
      }
      return result;
    }
   
    public Vector<Vector<Object>> tablepurchase()
   {
       Vector<Vector<Object>> vttablepurchase =new Vector<Vector<Object>>();
       try
       {
           Connection con=ConecttoDatabase.getConnection();
           String query="select BillId, BillName, t2.VenderName, t3.AccountName, BuildlDate, State, Unit, TotalCost from PurchaseBill t1, Vender t2, Administrative t3 where t1.VenderId=t2.VenderId and t1.AccountId=t3.AccountId ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
                    {
                        Vector<Object> rows=new Vector<Object>();
                        rows.add(rs.getInt("BillId"));
                        rows.add(rs.getString("BillName"));
                        rows.add(rs.getString("VenderName"));
                        rows.add(rs.getString("AccountName"));
                        SimpleDateFormat sp=new SimpleDateFormat("MM-dd-yyyy");
                        rows.add(sp.format(rs.getDate("BuildlDate")));
                        rows.add(rs.getString("State"));
                        rows.add(rs.getString("Unit"));
                        rows.add(rs.getDouble("TotalCost"));
                        vttablepurchase.add(rows);   
                }
       }
       catch(Exception ex)
       {
           System.out.println(ex);
           vttablepurchase=null;
       }
       return vttablepurchase;
   }
    public boolean DeleteBpurchasebill()
   {
       boolean result=true;
       try
       {
           Connection con=ConecttoDatabase.getConnection();
           String query="delete from PurchaseBill where BillId=?";
           PreparedStatement ps=con.prepareCall(query);
           ps.setInt(1, this.BillId);
           result=ps.executeUpdate()>0;
       }
       catch(Exception ex)
       {
           System.out.println("sai o deletepc"+ex);
           result=false;
       }
       return result;
   }
     public boolean UpdateBpurchase()
   {
       boolean result=true;
       try
       {
           Connection con=ConecttoDatabase.getConnection();
           String query="update PurchaseBill set  BillName = ?, VenderId = ?, AccountId = ?, BuildlDate = ?, State = ?, Unit = ?, TotalCost = ? where BillId like ?";
           PreparedStatement ps=con.prepareStatement(query);
           ps.setString(1, BillName);
           ps.setInt(2, VenderId);
           ps.setInt(3, AccountId);
           java.sql.Date jsd=new Date(this.BuildlDate.getTime());
           ps.setDate(4,jsd );
           
           ps.setString(5, State);
           ps.setString(6, Unit);
           ps.setDouble(7, TotalCost);
           ps.setInt(8, BillId);
           result=ps.executeUpdate()>0;
       }
       catch(Exception ex)
       {
           System.out.println(ex);
           result=false;
       }
       return result;
   }

    /**
     * @return the BillName
     */
    public String getBillName() {
        return BillName;
    }

    /**
     * @param BillName the BillName to set
     */
    public void setBillName(String BillName) {
        this.BillName = BillName;
    }

    /**
     * @return the State
     */
    public String getState() {
        return State;
    }

    /**
     * @param State the State to set
     */
    public void setState(String State) {
        this.State = State;
    }

    /**
     * @return the Unit
     */
    public String getUnit() {
        return Unit;
    }

    /**
     * @param Unit the Unit to set
     */
    public void setUnit(String Unit) {
        this.Unit = Unit;
    }

    /**
     * @return the TotalCost
     */
    public double getTotalCost() {
        return TotalCost;
    }

    /**
     * @param TotalCost the TotalCost to set
     */
    public void setTotalCost(double TotalCost) {
        this.TotalCost = TotalCost;
    }
      public static PurchaseBill getpurcharbill()
   {
       PurchaseBill pc=new PurchaseBill();
       try
       {
             Connection con=ConecttoDatabase.getConnection();
            String query="select top(1)* from PurchaseBill order by BillId desc ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
            {
                pc.setBillId(rs.getInt("BillId"));
            }
       }
       catch(Exception ex)
       {
           pc=null;
           System.out.println(ex);
       }
       return pc;
   }
}
